<?php
namespace app\common\traits;
use think\facade\Db;
use think\facade\Log;
use think\facade\Request;

/**
 * 对多表数据关联查询处理
 * Trait throwError
 * @package app\traits
 */
trait recordAllRun{


    /**
     * 下拉分页默认参数
     * @var array
     */
    public $RECORD_ALL_DEFAULT_recordAllPageByDropDown = [
        'orderKey'  => 'created_time',  //默认排序字段，不存在则自动按主键排序
        'limit'     => 10,   //每页显示数量
        'pageParam' => 'p', //get分页参数
    ];

    /**
     * 后面可以做缓存
     * @param $tablename
     * @return mixed
     */
    private function recordAllRunFetchTableFields($tablename) {
        return Db::query("show COLUMNS FROM " . $tablename);

    }

    /***
     * 对表进行单表或关联查询、统计、单或多数据查询处理
     * @param array $searchParams
     * @param array $mainTable
     * @param array $showFieldsList
     * @param array $tableJoins
     * @return array|null
     */
    protected function recordAllRun($searchParams = [],$showFieldsList = [],$tableJoins = []) {
        $tableAlias = static::getTable();

        /************* 获取字段列表 ***********/
        if(empty($showFieldsList)) {
            $fieldsTmp = $this->recordAllRunFetchTableFields($tableAlias);
            foreach($fieldsTmp as $fieldDetails) {
                $showFieldsList[$fieldDetails['Field']] = $tableAlias;
            }

            // 获取字段，并设置表joins
            foreach($tableJoins as $key=>$value) {
                $fieldsTmp = $this->recordAllRunFetchTableFields($value[0]);
                foreach($fieldsTmp as $fieldDetails) {
                    $showFieldsList[$key . '-' . $fieldDetails['Field']] = [$key,$fieldDetails['Field']];
                }
                $value[0] .= ' ' . $key;
                $tableJoins[$key] = $value;
            }
        }
        /*********** END 获取字段列表 *********/


        /******** 设置参数 *********/
        $whereTmp = isset($searchParams[0])?$searchParams[0]:[];
        $limit = isset($searchParams[1])?$searchParams[1]:[];
        $orderTmp = isset($searchParams[2])?$searchParams[2]:[];
        $fieldsTmp = isset($searchParams[3])?$searchParams[3]:[];

        //设置查询字段为*时，查询所有设置的字段
        if($fieldsTmp == '*') {
            $fieldsTmp = [];
            foreach($showFieldsList as $key=>$del)
                $fieldsTmp[] = $key;
        } elseif(is_array($fieldsTmp) && !empty($fieldsTmp)) {
            $tmpTableFields = [];
            $fieldsTables = [];
            foreach($fieldsTmp as $tmp) {
                if(strpos($tmp,'.*')) { //判断是否查询表全部字段（存在设置的字段）
                    $fieldsTables[] = rtrim($tmp,'.*');
                    continue;
                }
                $tmpTableFields[] = $tmp;
            }

            if(!empty($fieldsTables)) { //获取查所有字段的表
                foreach($showFieldsList as $key=>$val) {
                    if((is_string($val) && in_array($val,$fieldsTables)) || (is_array($val) && in_array($val[0],$fieldsTables)))
                        $tmpTableFields[] = $key;
                }
            }
            $fieldsTmp = $tmpTableFields;
        }
        /******** END 设置参数 *****/

        $where = $whereTmp;
        $fields = [$tableAlias . '.*'];    //默认查询字段
        $order = [];        //默认排序条件
        $queryTables = [];

        /********  处理排序字段 ***********/
        if(is_array($orderTmp)) {
            $order = [];
            foreach($orderTmp as $field=>$sort) {
                if(isset($showFieldsList[$field])) {
                    if(is_array($showFieldsList[$field])) {
                        $queryTables[] = $showFieldsList[$field][0];
                        $order[] = $showFieldsList[$field][0] . '.' . $showFieldsList[$field][1] . ' ' . $sort;
                    } else {
                        $queryTables[] = $showFieldsList[$field];
                        $order[] = $showFieldsList[$field] . '.' . $field . ' ' . $sort;
                    }
                }
            }
            $order = implode(',',$order);
        } else if(is_string($orderTmp)) {
            $order = $orderTmp;
        }
        /******  END 处理排序字段 *********/




        /*********** 整理查询的字段 ************/
        if(!empty($fieldsTmp)) {
            $fields = [];
            foreach($fieldsTmp as $field) {
                if(isset($showFieldsList[$field])) {
                    if(is_array($showFieldsList[$field])) {
                        $queryTables[] = $showFieldsList[$field][0];
                        $fields[] = $showFieldsList[$field][0] . '.' . $showFieldsList[$field][1] . ' `' . $field . '`';
                    } else {
                        $queryTables[] = $showFieldsList[$field];
                        $fields[] = $showFieldsList[$field] . '.' . $field . ' `' . $field . '`';
                    }
                }
            }
            if(!in_array($tableAlias,$queryTables))   //未指定主表字段时查询所有字段
                $fields[] = $tableAlias . '.*';

        }
        /********* END 整理查询的字段 **********/

        /*********** 整理查询条件 ************/
        $getField = function ($field,$showField) {
            if(is_array($showField)) {
                $table = $showField[0];
                $field = $showField[0] . '.' . $showField[1];
            } else {
                $table = $showField;
                $field = $showField . '.' . $field;
            }
            return [$field,$table];
        };
        if(is_array($whereTmp)) {
            $showFieldsListTmp = [];
            foreach($showFieldsList as $key=>$value) {
                if(!is_array($value)) {
                    $value = [$value,$key];
                }
                $showFieldsListTmp[$key] = implode('.',$value);
            }

            $strtrFunc = function ($value) use(&$strtrFunc,$showFieldsListTmp) {
                if(is_array($value[0])) {
                    foreach($value as $key=>$val) {
                        $value[$key] = $strtrFunc($val);
                    }
                    return $value;
                }
                $value[0] = array_key_exists($value[0],$showFieldsListTmp)?$showFieldsListTmp[$value[0]]:$value[0];
                return $value;
            };
            $where = [];
            foreach($whereTmp as $field=>$val) {
                if(is_integer($field)){
                    $where[] = $strtrFunc($val);
                } else if(isset($showFieldsList[$field])) {
                    list($newField,$queryTables[]) = $getField($field,$showFieldsList[$field]);
                    $where[$newField] = $val;
                } else {    //特殊字段处理
                    if(preg_match("/\|/",$field)) { //多字段条件搜索
                        $tmpFields = explode('|',$field);
                        $newTmpFields = [];
                        foreach($tmpFields as $field) {
                            if(isset($showFieldsList[$field])) {
                                list($newField,$queryTables[]) = $getField($field,$showFieldsList[$field]);
                                $newTmpFields[] = $newField;
                            }
                        }
                        if(!empty($newTmpFields))
                            $where[implode('|',$newTmpFields)] = $val;
                    }

                }
            }
        } elseif(!empty($whereTmp)) {
            $where = $whereTmp;
        }
        /********* END 整理查询条件 **********/


        //去掉必须的表
        $queryTables = array_unique($queryTables);

        $delKey = array_search($tableAlias,$queryTables);
        if($delKey !== false)
            unset($queryTables[$delKey]);

        if(!empty($queryTables)) {  //对关联表进行排序
            $tmpSort = array_keys($tableJoins);
            $newTmpTable = [];
            foreach($tmpSort as $tmp) {
                if(in_array($tmp,$queryTables))
                    $newTmpTable[] = $tmp;
            }
            $queryTables = $newTmpTable;
        }

        $query = Db::table($tableAlias)->alias($tableAlias);
        foreach($queryTables as $table) {   //循环添加关联查询表
            if(isset($tableJoins[$table]))
                $query = call_user_func_array([$query,'join'],$tableJoins[$table]);
        }

        $query = $query->where($where);

        if($limit === 0)    //输出天统计数据
            return $query->count();

        if(!empty($order))  //排序
            $query = $query->order($order);

        if($limit === 1) {  //获取单条记录输出
            return ($info = $query->field($fields)->find())?$info:null;
        }

        if(!empty($limit))  //获取数量
            $query = $query->limit($limit[0],$limit[1]);

        $query = $query->field($fields)->select()->toArray();
        Log::write($query,'notice');

        $result = [];
        foreach($query as $value) {
            $result[] = $value;
        }
        return $result;
    }


    /**
     * 下拉分页
     * @param null $where 查询条件
     * @param int|null $pageLimit 每页查询数量
     * @param array|null $order 排序
     * @param array|null $fields    查询字段（会额外增加主键及排序字段）
     * @param array|null $joinTable 关联表
     * @param int|null $pageParam get的分页参数
     * @return array 结果集 [list=>结果集合,nextPage=>下一页参数]
     */
    public function recordAllPageByDropDown ($where = null,int $pageLimit = null,array $order = null,array $fields = null,array $joinTable = null,int $pageParam = null)
    {
        $fields = is_null($fields)?[]:$fields;
        $fields[] = static::getTable() . '.*';
        // 获取默认值
        $default = $this->RECORD_ALL_DEFAULT_recordAllPageByDropDown;

        /**
         * 下一页参数转换
         * @param $value
         * @return bool|mixed|string
         */
        $pageParamTransferFunc = function($value){
            $result = false;
            if(empty($value)) { // 空返回
                return $result;
            }

            if(is_string($value)) { //还原
                $result = json_decode(base64_decode($value),true);
                $result = is_null($result)?false:$result;
            } else {    // 数组转字符串
                $result = base64_encode(json_encode($value));
            }
            return $result;
        };
        // 获取表结构
        $schema = $this->recordAllRunFetchTableFields(static::getTable());
        // 获取苏字段
        $columnNames = array_column($schema,'Field');
        // 获取主键
        $uniqueKey = null;
        foreach($schema as $val) {
            if($val['Key'] == 'PRI') {
                $uniqueKey = $val['Field'];
                break;
            }
        }


        // 设置每页数量
        $pageLimit = is_null($pageLimit)?$default['limit']:$pageLimit;

        // 设置默认排序字段
        if(is_null($order)) {
            $defaultOrder = $default['orderKey'];
            $order = in_array($defaultOrder,$columnNames)?[$defaultOrder=>'DESC']:[$uniqueKey=>'DESC'];
        }



        // 设置显示的查询参数
        $pageParam = is_null($pageParam)?$default['pageParam']:$pageParam;

        // 设置返回的结果
        $result = [
            'list'  => [],
            'nextPage'  => [
                'param' => $pageParam,
                'value' => '',
            ],
        ];


        /*********** 增加下一页条件 ************/
        if(($page = $pageParamTransferFunc(Request::get($pageParam))) && is_array($page) &&
            array_key_exists(0,$page) && array_key_exists(1,$page) &&
            count($page[0]) == count($order)) {   // 判断是否非第一页
            $tmpWhere = [];
            if(!empty($where)) {    // 保留原条件
                $tmpWhere[] = $where;
            }
            $i = 0;
            foreach($order as $key=>$sort) {
                $keyWhere = '';
                switch (strtoupper($sort)) {
                    case 'DESC':
                        $keyWhere = '<=';
                        break;
                    case 'ASC':
                        $keyWhere = '>=';
                        break;
                }
                $tmpWhere[] = [$key,$keyWhere,$page[0][$i]];
                ++$i;
            }
            $tmpWhere[] = [$uniqueKey ,'not in',$page[1]];
            $where = $tmpWhere;
        }
        /********* END 增加下一页条件 **********/


        // 查询字段增加排序字段
        $fields = empty($fields)?['*']:$fields;
        $fields = array_merge($fields,array_keys($order),[$uniqueKey]);

        // 查询获取结果
        if(!($result['list'] = $this->recordAll($where,[0,$pageLimit + 1],$order,$fields,$joinTable)) || count($result['list']) <= $pageLimit) {
            return $result; // 无
        }
        // 需要分页处理（去掉最后一个记录）
        array_pop($result['list']);
        // 获取下一页参数
        $getPageFunc = function($result) use($order,$uniqueKey,$pageParamTransferFunc) {
            $orderKeys = json_decode(strtr(json_encode(array_keys($order)),['`'=> '']),true);
            $result = array_reverse($result);
            $pageList = [];
            foreach($result as $value) {
                $orderValue = [];
                foreach($orderKeys as $orderKey) {
                    $orderValue[] = $value[$orderKey];
                }
                $pageKey = md5(json_encode($orderValue));
                if(empty($pageList)) {
                    $pageList[$pageKey] = [
                        $orderValue,
                        [],
                    ];
                }

                if(!array_key_exists($pageKey,$pageList)) {
                    break;
                }
                $pageList[$pageKey][1][] = $value[$uniqueKey];
            }

            $page = array_shift($pageList);
            return $pageParamTransferFunc($page);
        };
        // 获取下一页参数
        $result['nextPage']['value'] = $getPageFunc($result['list']);
        return $result;
    }



}
